package br.com.as.dao;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import br.com.as.dominio.Cliente;
import br.com.as.utils.HibernateUtility;

/**
 * Classe para consistencia de cliente
 * 
 * @author msante
 *
 */
public class ClienteDAO {

	@SuppressWarnings("unchecked")
	public  List<Cliente> listar() throws Exception{
		List<Cliente> lista = null;
		Session sessao = HibernateUtility.getSession();
		try {
			Query query = sessao.createQuery(" from Cliente ");		
			lista = query.list();			
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
		return lista;
	}
	
	public  void incluir(Cliente cliente) throws Exception{
		Transaction tx = null;
		Session sessao = HibernateUtility.getSession();
		try {			
			tx = sessao.beginTransaction();
			sessao.save(cliente.getEndereco());
			sessao.save(cliente.getContato());
			sessao.save(cliente);
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			sessao.close();
			throw e;
		}finally{
			sessao.close();	
		}
	}
	
	public  void alterar(Cliente cliente) throws Exception{
		Transaction tx = null;
		Session sessao = HibernateUtility.getSession();
		try {			
			tx = sessao.beginTransaction();
			sessao.saveOrUpdate(cliente.getEndereco());
			sessao.saveOrUpdate(cliente.getContato());
			sessao.update(cliente);
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			sessao.close();
			throw e;
		}finally{
			sessao.close();	
		}
	}

	public void excluir(Cliente cliente) throws Exception{
		Transaction tx = null;
		Session sessao = HibernateUtility.getSession();
		try {			
			tx = sessao.beginTransaction();
			sessao.delete(cliente);
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			sessao.close();
			throw e;
		}finally{
			sessao.close();	
		}
	}

	public  Cliente consultar(String cpf, String cnpj) throws Exception{
		Cliente cliente = null;
		Session sessao = HibernateUtility.getSession();
		try {
			StringBuffer cpfCnpj = new StringBuffer("");
			StringBuffer sql = new StringBuffer("");
			if(cnpj != null && !cnpj.trim().equalsIgnoreCase("")){
				sql.append("from Cliente cli where cli.cnpj =:cpfCnpj");
				cpfCnpj.append(cnpj);
			} else if(cpf != null && !cpf.trim().equalsIgnoreCase("")){
				sql.append("from Cliente cli where cli.cpf =:cpfCnpj");
				cpfCnpj.append(cpf);
			} else {
				return null;
			}
				
			Query query = sessao.createQuery(sql.toString());		
			query.setString("cpfCnpj", cpfCnpj.toString());
			cliente = (Cliente) query.uniqueResult();			
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
		return cliente;		
	}

	@SuppressWarnings("unchecked")
	public  List<Cliente> aniversariantes(String mes) throws Exception{
		Session sessao = HibernateUtility.getSession();
		try {
			Query query = sessao.createQuery("from Cliente WHERE MONTH(dataNascimento) =:mes");		
			query.setString("mes", mes);
			return query.list();
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
	}

	public Cliente pesquisarPorId(Long id) throws Exception{
		Session sessao = HibernateUtility.getSession();
		try {
			Cliente temp = (Cliente) sessao.get(Cliente.class, id);
			return temp;
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
	}
	
	@SuppressWarnings("unchecked")
	public  List<Cliente> listarOrderByDataNascimento() throws Exception{
		List<Cliente> lista = null;
		Session sessao = HibernateUtility.getSession();
		try {
			Query query = sessao.createQuery(" from Cliente c order by c.dataNascimento desc ");		
			lista = query.list();			
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
		return lista;
	}
	
//	@SuppressWarnings("unchecked")
//	public  List<Cliente> listarOrderByDataNascimento() throws Exception{
//		List<Cliente> lista = null;
//		Session sessao = HibernateUtility.getSession();
//		try {
//			Query query = sessao.createSQLQuery(" SELECT distinct(DATE_FORMAT(data ,'%Y-%m')) as anoMes FROM agilesystem.venda v order by data desc ");		
//			lista = query.list();			
//		} catch (Exception e) {			
//			throw e;
//		} finally {
//			sessao.close();
//		}
//		return lista;
//	}
}
